<%@ page import="javax.sql.DataSource" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.lang.reflect.Method" %>
<%@ page import="java.lang.reflect.Field" %>
<%@ page import="com.company.cop.annotation.DBColumn" %>
<%@ page import="java.util.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ include file="db.jsp" %>
<%!
    @Deprecated
    private void _dao_init() {

    }

    /**
     * 执行 sql
     * @param sql
     * @return
     * @throws SQLException
     */
    private boolean _dao_execute(String sql) throws SQLException {
        return _dao_execute(sql, new String[]{});
    }

    /**
     * 执行 sql
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    private boolean _dao_execute(String sql, String[] parameters) throws SQLException {
        List _ = new ArrayList();
        int i = 1;
        for (String parameter : parameters) {
            _.add(parameter);
        }
        return _dao_execute(sql, _);
    }

    /**
     * 执行 sql
     * @param sql
     * @param parameters
     * @return main
     * @throws SQLException
     */
    private boolean _dao_execute(String sql, List<String> parameters) throws SQLException {
        PreparedStatement ps = null;
        boolean result = true;
        Map bean;

        try {
            //执行

            ps = _db_conn.get().prepareStatement(sql);

            int i = 1;
            for (String parameter : parameters) {
                ps.setString(i++, parameter);
            }

            result = ps.execute();

            _db_destory(null, ps, null);

        } catch (Exception e) {
            try {
                _db_destory(null, ps, null);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw new SQLException(e);
        } finally {
            try {
                _db_destory(null, ps, null);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return result;
    }

    /**
     * select segment
     */

    /**
     * 取 List<Map>
     * @param sql
     * @return
     */
    private List<Map> _dao_getList(String sql) throws SQLException {
        return _dao_getList(sql, new String[]{});
    }

    /**
     * 通过 String[] 取 List<Map>
     * @param sql
     * @param parameters
     * @return
     */
    private List<Map> _dao_getList(String sql, String[] parameters) throws SQLException {
        List _ = new ArrayList();
        for (String parameter : parameters) {
            _.add(parameter);
        }
        return _dao_getList(sql, _);
    }

    /**
     * 通过 List 取 List<Map>
     * @param sql
     * @param parameters
     * @return main
     */
    private List<Map> _dao_getList(String sql, List<String> parameters) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List result = new ArrayList();
        Map bean;

        try {
            //取数据

            ps = _db_conn.get().prepareStatement(sql);

            int i = 1;
            for (String parameter : parameters) {
                ps.setString(i++, parameter);
            }

            rs = ps.executeQuery();

            Set<String> set = new HashSet();
            ResultSetMetaData _ = rs.getMetaData();
            for (i = 1; i <= _.getColumnCount(); i++) {
                set.add(_.getColumnName(i));
            }

            while (rs.next()) {
                bean = new HashMap();
                for (String column : set) {
                    bean.put(column, rs.getString(column));
                }
                result.add(bean);
            }

            _db_destory(null, ps, rs);

        } catch (Exception e) {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw new SQLException(e);
        } finally {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 取 List
     * @param sql
     * @param className
     * @return
     */
    private List _dao_getList(String sql, String className) throws SQLException {
        return _dao_getList(sql, className, new String[]{});
    }

    /**
     * 通过 String[] 取 List
     * @param sql
     * @param className
     * @param parameters
     * @return
     */
    private List _dao_getList(String sql, String className, String[] parameters) throws SQLException {
        List<String> _ = new ArrayList();
        for (String parameter : parameters) {
            _.add(parameter);
        }
        return _dao_getList(sql, className, _);
    }

    /**
     * 通过 List 取 List
     * @param sql
     * @param className
     * @param parameters
     * @return main
     */
    private List _dao_getList(String sql, String className, List<String> parameters) throws SQLException {

        PreparedStatement ps = null;
        ResultSet rs = null;
        Object bean;
        List result = new ArrayList();

        try {
            //取配置信息
            Class cls = Class.forName(className);
            Method method;
            String methodName;
            String columnName;
            String fieldName;
            HashMap<String, Method> methods = new HashMap();
            Field[] fields = cls.getDeclaredFields();
            for (Field field : fields) {
                if (field.getAnnotation(DBColumn.class) != null) {
                    columnName = (field.getAnnotation(DBColumn.class)).value();
                    fieldName = field.getName();
                    methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    method = cls.getMethod(methodName, new Class[]{String.class});
                    methods.put(columnName, method);
                }
            }

            //取数据
            ps = _db_conn.get().prepareStatement(sql);

            int i = 1;
            for (String parameter : parameters) {
                ps.setString(i++, parameter);
            }

            rs = ps.executeQuery();

            Set<String> set = new HashSet();
            ResultSetMetaData _ = rs.getMetaData();
            for (i = 1; i <= _.getColumnCount(); i++) {
                set.add(_.getColumnName(i));
            }

            while (rs.next()) {
                bean = cls.newInstance();
                for (String column : set) {
                    method = methods.get(column);
                    if (method != null) {
                        method.invoke(bean, new Object[]{rs.getString(column)});
                    }
                }
                result.add(bean);
            }

            _db_destory(null, ps, rs);

        } catch (Exception e) {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw new SQLException(e);
        } finally {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 取 Map
     * @param sql
     * @return
     */
    private Map _dao_getObject(String sql) throws SQLException {
        return _dao_getObject(sql, new String[]{});
    }

    /**
     * 通过 String[] 取 Map
     * @param sql
     * @param parameters
     * @return
     */
    private Map _dao_getObject(String sql, String[] parameters) throws SQLException {
        List _ = new ArrayList();
        for (String parameter : parameters) {
            _.add(parameter);
        }
        return _dao_getObject(sql, _);
    }

    /**
     * 通过 List 取 Map
     * @param sql
     * @param parameters
     * @return main
     */
    private Map _dao_getObject(String sql, List<String> parameters) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String, String> result = null;

        try {

            //取数据
            ps = _db_conn.get().prepareStatement(sql);

            int i = 1;
            for (String parameter : parameters) {
                ps.setString(i++, parameter);
            }

            rs = ps.executeQuery();
            Set<String> set = new HashSet();
            ResultSetMetaData _ = rs.getMetaData();
            for (i = 1; i <= _.getColumnCount(); i++) {
                set.add(_.getColumnName(i));
            }

            if (rs.next()) {
                result = new HashMap();
                for (String column : set) {
                    result.put(column, rs.getString(column));
                }
            }

            _db_destory(null, ps, rs);

        } catch (Exception e) {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw new SQLException(e);
        } finally {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 取 Object
     * @param sql
     * @param className
     * @return
     */
    private Object _dao_getObject(String sql, String className) throws SQLException {
        return _dao_getObject(sql, className, new String[]{});
    }

    /**
     * 通过 String[] 取 Object
     * @param sql
     * @param className
     * @param parameters
     * @return
     */
    private Object _dao_getObject(String sql, String className, String[] parameters) throws SQLException {
        List _ = new ArrayList();
        for (String parameter : parameters) {
            _.add(parameter);
        }
        return _dao_getObject(sql, className, _);
    }

    /**
     * 通过 List 取 Object
     * @param sql
     * @param className
     * @param parameters
     * @return main
     */
    private Object _dao_getObject(String sql, String className, List<String> parameters) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Object result = null;

        try {
            //取配置信息
            Class cls = Class.forName(className);
            Method method;
            String methodName;
            String columnName;
            String fieldName;
            HashMap<String, Method> methods = new HashMap();
            Field[] fields = cls.getDeclaredFields();
            for (Field field : fields) {
                if (field.getAnnotation(DBColumn.class) != null) {
                    columnName = (field.getAnnotation(DBColumn.class)).value();
                    fieldName = field.getName();
                    methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    method = cls.getMethod(methodName, new Class[]{String.class});
                    methods.put(columnName, method);
                }
            }

            //取数据
            ps = _db_conn.get().prepareStatement(sql);

            int i = 1;
            for (String parameter : parameters) {
                ps.setString(i++, parameter);
            }

            rs = ps.executeQuery();
            Set<String> set = new HashSet();
            ResultSetMetaData _ = rs.getMetaData();
            for (i = 1; i <= _.getColumnCount(); i++) {
                set.add(_.getColumnName(i));
            }

            if (rs.next()) {
                result = cls.newInstance();
                for (String column : set) {
                    method = methods.get(column);
                    if (method != null) {
                        method.invoke(result, new Object[]{rs.getString(column)});
                    }
                }
            }

            _db_destory(null, ps, rs);

        } catch (Exception e) {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw new SQLException(e);
        } finally {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 通过 beanConfig 取 Object
     * @param sql
     * @param className bean 的类名字
     * @param beanConfig
     * @return main
     */
    @Deprecated
    private Object _dao_getObject(String sql, String className, Map<String, String> beanConfig) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Object result = null;

        try {

            ps = _db_conn.get().prepareStatement(sql);
            rs = ps.executeQuery();

            Class cls = Class.forName(className);
            Method method;
            HashMap<String, Method> methods = new HashMap();
            Set<String> set = beanConfig.keySet();
            String property;
            String methondName;
            //IOC设置
            for (String column : set) {
                property = beanConfig.get(column);
                methondName = "set" + property.substring(0, 1).toUpperCase() + property.substring(1);
                methods.put(column, cls.getMethod(methondName, new Class[]{String.class}));
            }

            //取数据
            if (rs.next()) {
                result = cls.newInstance();
                for (String column : set) {
                    method = methods.get(column);
                    method.invoke(result, new Object[]{rs.getString(column)});
                }
            }

            _db_destory(null, ps, rs);

        } catch (Exception e) {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw new SQLException(e);
        } finally {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 取 String
     * @param sql
     * @return
     */
    private String _dao_getString(String sql) throws SQLException {
        return _dao_getString(sql, new String[]{});
    }

    /**
     * 通过 String[] 取 String
     * @param sql
     * @param parameters
     * @return
     */
    private String _dao_getString(String sql, String[] parameters) throws SQLException {
        List _ = new ArrayList();
        for (String parameter : parameters) {
            _.add(parameter);
        }
        return _dao_getString(sql, _);
    }

    /**
     * 通过 List 变量取 String
     * @param sql
     * @param parameters
     * @return main
     */
    private String _dao_getString(String sql, List<String> parameters) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String result = "";
        try {

            ps = _db_conn.get().prepareStatement(sql);

            int i = 1;
            for (String parameter : parameters) {
                ps.setString(i++, parameter);
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getString(1);
            }
            
            _db_destory(null, ps, rs);

        } catch (Exception e) {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw new SQLException(e);
        } finally {
            try {
                _db_destory(null, ps, rs);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 测试数据库连接是否正常
     */
    @Deprecated
    private void _dao_test_connection() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = _db_getConnection();
            System.out.println("conn = " + conn);
            ps = conn.prepareStatement("select 1 from dual");
            rs = ps.executeQuery();
            System.out.println("rs.next() = " + rs.next());
        } catch (Exception e) {
            try {
                _db_destory(conn, ps, rs);
            } catch (Exception e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                _db_destory(conn, ps, rs);
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
    }
%>

